<?php
    /*
     * Connect to database
     */
    $con = mysqli_connect('127.0.0.1', "root", "", "crud");
    if(mysqli_connect_errno($con))
    {
        echo "Error in connecting to MySQL: " . mysqli_connect_error() . "<br/>";
    }

    /*
     * Insert new entry in database
     */
    $errorEmailExists = "";
    if(isset($_POST['insert']) && !empty($_POST['insert']))
            {
                $name = mysql_escape_string($_POST['name']);
                $surname = mysql_escape_string($_POST['surname']);
                $email = mysql_escape_string(filter_var($_POST['email'], FILTER_VALIDATE_EMAIL));
                $telephone = mysql_escape_string(filter_var($_POST['telephone'], FILTER_VALIDATE_INT));
                if(!$name || !$surname || !$email ||!$telephone)
                {
                    echo "Please provide valid input";
                }
                else
                {
                    /*
                     *Email should be unique
                     * Verify if the provided email exist in the database 
                     */
                    $sqlEmail = "SELECT * FROM users WHERE email = '" . $email ."'";
                    $resultEmail = mysqli_query($con,$sqlEmail);
                    
                    while ($row = mysqli_fetch_array($resultEmail))
                    {
                        if($row['email'] != null)
                        {
                            $errorEmailExists = "<p style=\"color:red\"> Email already exists.</p><br/>";
                        }
                    }
                    $sqlInsert = "INSERT INTO users(name, surname, email, telephone) 
                                VALUES ('$name', '$surname', '$email', '$telephone')";
                    $resultInsert = mysqli_query($con, $sqlInsert);
                   
                    
//                    if(!$resultInsert)
//                    {
//                       echo "Error in query: " . mysqli_errno($con);
//                    }
                }
            }
    /*
     *Delete from database 
     */
    if(isset($_POST['delete']) && !empty($_POST['delete']))
                   {
                       $sqlDelete = "DELETE FROM users WHERE id='" . $_POST['hiddenDelete'] . "'";
                       $resultDelete = mysqli_query($con, $sqlDelete);
                       if(!$resultDelete)
                       {
                           echo 'Error in query: ' . mysqli_error($con);
                       }
                   }
     /*
      * Update 
      */
      
    /*
     * If the Save button was pressed when updating a field
     */
    if(isset($_POST['save']) && !empty($_POST['save']))
    {
        $updateName = mysql_escape_string($_POST['updateName']);
        $updateSurname = mysql_escape_string($_POST['updateSurname']);
        $updateEmail = mysql_escape_string(filter_var($_POST['updateEmail'], FILTER_VALIDATE_EMAIL));
        $updateTelephone = mysql_escape_string(filter_var($_POST['updateTelephone'], FILTER_VALIDATE_INT));
        if(!$updateName || !$updateSurname || !$updateEmail ||!$updateTelephone)
        {
            echo "<p style=\"color:red\">Please provide valid input</p>";
        }
        else
        {
            /*
            *Email should be unique
            * Verify if the provided email exist in the database 
            */
           $sqlEmail = "SELECT * FROM users WHERE email = '" . $updateEmail ."' AND id!='" . $_POST['hiddenSave'] . "'";
           $resultEmail = mysqli_query($con,$sqlEmail);

           while ($row = mysqli_fetch_array($resultEmail))
           {
               if($row['email'] != null)
               {
                   echo "<span style=\"color:red\"> Email already exists.</span><br/>";
               }
           }
            $sqlUpdate = "UPDATE users 
                            SET name='$updateName',surname='$updateSurname',email='$updateEmail',telephone='$updateTelephone'
                            WHERE id = '" . $_POST['hiddenSave'] ."'";
            $resultUpdate = mysqli_query($con, $sqlUpdate);
//            if(!$resultUpdate)
//            {
//                echo "Error in query: " . mysqli_errno($con);
//            }
        }
        
    }
                   
          
?>
<!doctype html>
<html>
    <head>
        <title>CRUD</title>
    </head>
    <body>
        <?php
            /*
            * Select the name, surname, email and telephone number from the database
            * And data from table on the screen inside a table, row by row
            */
            $sqlSelect = "SELECT id,name, surname, email, telephone FROM users";
            $resultSelect = mysqli_query($con, $sqlSelect);
            if(!$resultSelect)
            {
                echo "Error in query: " . mysqli_error($con) . "<br/>";
            }
            else
            {
                echo "<table border='2'>" . "<tr>" . 
                        "<th>Name</th>" . 
                        "<th>Surname</th>" . 
                        "<th>Email</th>" .
                        "<th>Telephone</th>" .
                        "<th>Actions</th>" ;
                while($row = mysqli_fetch_array($resultSelect)) 
                {
                    $pressedField = 0;
                    if(isset($_POST['update']) && !empty($_POST['update']))
                    {
                        /*
                         * If the update button was pressed
                         */
                        if($row['id'] == $_POST['hiddenUpdate'])
                        {
                            echo "<tr>" . 
                                    "<td>
                                         <form action='' method='post'>
                                         <input type='text' name='updateName' value='" . $row['name'] . "'</td>" . 
                                    "<td><input type='text' name='updateSurname' value='" . $row['surname'] . "'</td>" . 
                                    "<td><input type='text' name='updateEmail' value='" . $row['email'] . "'</td>" . 
                                    "<td><input type='text' name='updateTelephone' value='" . $row['telephone'] . "'</td>" . 
                                    "<td><input type='submit' name='save' value='Save'/>
                                         <input type='hidden' name='hiddenSave' value='" . $row['id'] . "'/> 
                                         </form>
                                    </td>" .
                                 "</tr>"; 
                            //We save the id of the pressed field
                            $pressedField = $row['id'];
                        }    
                      }
                      
                      /*
                       * Print all the data from table, except for the row that we want updated
                       * That one is printed above
                       */
                      if($row['id'] != $pressedField)
                      {
                           echo "<tr>" . 
                            "<td>" . $row['name'] . "</td>" . 
                            "<td>" . $row['surname'] . "</td>" . 
                            "<td>" . $row['email'] . "</td>" . 
                            "<td>" . $row['telephone'] . "</td>" . 
                            "<td><form action='' method='post'>
                                 <input type='hidden' name='hiddenUpdate' value='" . $row['id'] . "'/>
                                 <input type='submit' name='update' value='Update'/>
                                 <input type='hidden' name='hiddenDelete' value='" . $row['id'] . "'/>
                                 <input type='submit' name='delete' value='Delete'/>
                                 </form>
                            </td>" .
                         "</tr>";  
                      }
                }
                echo "</table>" . "</tr>" ;
            }            
        ?>
        <p>
        Insert new user:
        </p>
        <form action="" method="post">
            <input type="text" name="name" placeholder="Name" />
            <input type="text" name="surname" placeholder="Surname" />
            <input type="text" name="email" placeholder="E-mail" />
            <input type="text" name="telephone" placeholder="Telephone" />
            <input type="submit" name="insert" value="Submit" />
        </form>
       <?php
            if($errorEmailExists)
            {
                echo $errorEmailExists;
            }
       ?>
    </body>
</html>
